package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MRefList;
import org.compiere.model.Mhrmemployee;
import org.compiere.model.Mhrmorder;
import org.compiere.model.X_C_BPartner;
import org.compiere.model.X_hrm_order;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Language;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;
import org.eevolution.model.X_HR_Employee;
import org.eevolution.model.X_HR_Job;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class DismissalReport extends SvrProcess {

	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private int HR_Department_ID = 0;
	/** */
	X_HR_Department block = null;
	/** */
	 X_HR_Department department = null;
	/** */
	private String path = "";
	/** */
	String contractterminationtype = "";
	/**	Employee List	*/
	private Mhrmemployee[] m_lines;
	/** Business trip order type */
	String orderType = X_hrm_order.HRM_ORDERTYPE_Dismissal;
	/** */
	Timestamp startDate;
	/** */
	Timestamp endDate;
	/** */
	private String isManagement = "N";
	
	@Override
	protected void prepare() {

		log.info(" Report Dismissal ");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("contractterminationtype") == 0)
				contractterminationtype = (String)para[i].getParameter();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("isManagement") == 0)
				isManagement = (String) para[i].getParameter();
			else if (name.compareTo("StartDate") == 0){
				startDate = (Timestamp) para[i].getParameter();
				endDate = (Timestamp) para[i].getParameter_To();
			}
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {

		// Types of cells for
		Label label;
		Number number;
		DateTime dateCell;
		// There are checks for a specified pattern
		String templateName = "SampleDismissal";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("List of dismissals").append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableCellFormat borderStyle = new WritableCellFormat();
		borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		borderStyle.setAlignment(Alignment.CENTRE);
		borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		borderStyle.setWrap(true);
		// Dismissal style cell
		WritableFont fontNormal = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
		WritableCellFormat disType = new WritableCellFormat(fontNormal);
		disType.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		disType.setAlignment(Alignment.CENTRE);
		disType.setVerticalAlignment(VerticalAlignment.CENTRE);
		disType.setBackground(Colour.TAN);
		// Title style cell
		WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
		WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
		titleStyle.setAlignment(Alignment.CENTRE);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		// Date format Setting
		DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy"); 
		WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
		dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		dateFormat.setAlignment(Alignment.CENTRE);
		dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		// Percent format
		WritableFont fontPercent = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
		WritableCellFormat percentf = new WritableCellFormat(fontPercent, NumberFormats.PERCENT_INTEGER);
		percentf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		percentf.setAlignment(Alignment.CENTRE);
		percentf.setVerticalAlignment(VerticalAlignment.CENTRE);
		
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents()+Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
		
		String managment = "";
		if(isManagement.equals("Y"))
	    	managment = "Управленческий состав ";
		
		// set title
		if(HR_Block_ID != 0)
			label = new Label(1, 2, managment+"Блока "+block.getName(), titleStyle);
		else
			label = new Label(1, 2, managment, titleStyle);
		sheet.addCell(label);
		
		Object[] param = new Object[]{Util.getMonthName(new Timestamp(startDate.getTime()), Env.getAD_Language(getCtx()), false), 
				  Integer.toString(new org.joda.time.DateTime(startDate.getTime()).getYear()),
				  Util.getMonthName(new Timestamp(endDate.getTime()), Env.getAD_Language(getCtx()), false),
				  Integer.toString(new org.joda.time.DateTime(endDate.getTime()).getYear())};
		if(new org.joda.time.DateTime(startDate.getTime()).getMonthOfYear() != new org.joda.time.DateTime(endDate.getTime()).getMonthOfYear())
			label = new Label(2, 3, sheet.getCell(2, 3).getContents()+MessageFormat.format(" {0} {1} - {2} {3} года", param), titleStyle);
		else
			label = new Label(2, 3, MessageFormat.format(" за {0} месяц {1} года", param), titleStyle);
		sheet.addCell(label);
		
		X_C_BPartner bp = null;
		X_HR_Employee he = null;
		X_HR_Department department = null;
		// Order Type 'BUSINESS TRIP'
		Mhrmemployee hrmemp = new Mhrmemployee(getCtx());
		m_lines = hrmemp.getHRMEmployeeList(HR_Block_ID, HR_Department_ID, orderType, contractterminationtype, startDate, endDate, isManagement);
		Mhrmorder hrmorder = null;
		int col = cellStart.getC()+1; // offset by the number of first columns
		int row = cellStart.getR();
		String tId = ""; // Type of trip
		int typeDis = 0;
		int num = 1;
		int inc = 0;
		int i = 0;
		for(int j = row; j < (row+m_lines.length); j++){
			
			// Title
			he = X_HR_Employee.getHR_Employee(m_lines[i].getC_BPartner_ID(),null); 
			bp = new X_C_BPartner(getCtx(),m_lines[i].getC_BPartner_ID(),get_TrxName());
			X_HR_Job job = new X_HR_Job(getCtx(), he.getHR_Job_ID(), get_TrxName());
			hrmorder = new Mhrmorder(m_ctx, m_lines[i].gethrm_order_ID(), get_TrxName());
			department = new X_HR_Department(getCtx(), he.getHR_Department_ID(), get_TrxName());
			// Merge cells by type of vacation
			if(!tId.equals(hrmorder.gethrm_contractterminationtype())){
				sheet.insertRow(j+typeDis);
		    	sheet.setRowView(j+typeDis, 600);
				sheet.mergeCells(1, j+typeDis, 8, j+typeDis);
				// Department
				label = new Label(1, j+typeDis, MRefList.getListName(getCtx(), X_hrm_order.HRM_CONTRACTTERMINATIONTYPE_AD_Reference_ID, hrmorder.gethrm_contractterminationtype()), disType); 
				sheet.addCell(label);
				tId = hrmorder.gethrm_contractterminationtype();
				typeDis++;
			}
			
			sheet.setRowView(j+typeDis, 550);
			// #
			number = new Number(cellStart.getC(), j+typeDis, num, borderStyle); 
			sheet.addCell(number);
			// initials
			label = new Label(col, j+typeDis, bp.getName(), borderStyle); 
			sheet.addCell(label);
			// Department
			label = new Label(col+1, j+typeDis, department.getName(), borderStyle); 
			sheet.addCell(label);
			// Job
			label = new Label(col+2, j+typeDis, job.getName(), borderStyle); 
			sheet.addCell(label);
			// Order number
			label = new Label(col+3, j+typeDis, hrmorder.gethrm_number(), borderStyle); 
			sheet.addCell(label);
			// Order Name
			label = new Label(col+4, j+typeDis, hrmorder.getName(), borderStyle); 
			sheet.addCell(label);
			// Dates
			dateCell = new DateTime(col+5, j+typeDis, hrmorder.getDateFrom(), dateFormat);
			sheet.addCell(dateCell);
			dateCell = new DateTime(col+6, j+typeDis, hrmorder.getStartDate(), dateFormat);
			sheet.addCell(dateCell);
			
			// shift the basement
		       sheet.insertRow(j+typeDis+1);
			
			i++; num++;
			inc =j+typeDis;
		}
		
		if(HR_Block_ID == 0 && isManagement.equals("N")){
			sheet.removeRow(2);
			inc--;
		}
		
		// BEGIN LOAD
		ExcelCell cellLoad =  Util.getCellStart(tableBook,">>>");
		Dismissal[] dismissal = getLoad(startDate, endDate, contractterminationtype); 
		int colD = cellLoad.getC(); // offset by the number of first columns
		int rowD = inc+7;
		int dId = 0; int jo = 0; int flag = 0;
		int l = 0;
		for(int k = rowD; k < (rowD+dismissal.length); k++){
			//
			sheet.setRowView(k, 550);
			
			if(l == 0)
				dId = dismissal[l].getDepartment_ID();
			
			department = new X_HR_Department(getCtx(), dismissal[l].getDepartment_ID(), get_TrxName());
			// Department
			label = new Label(colD, k, department.getName(), borderStyle); 
			sheet.addCell(label);
			// Date
			label = new Label(colD+1, k, Util.getMonthName(dismissal[l].getStartdate(), Language.AD_Language_ru_RU, false), borderStyle); 
			sheet.addCell(label);
			// Count dismissal
			number = new Number(colD+2, k, dismissal[l].getCntDismissal(), borderStyle); 
			sheet.addCell(number);
			// % load
			number = new Number(colD+3, k, dismissal[l].getCntDismissal()/dismissal[l].getFullDismissal(), percentf); 
			sheet.addCell(number);
			
			// combine the match
	    	if(dId == dismissal[l].getDepartment_ID())
	    	   if(flag == 0)
		    		flag = k;
		    	else
		    		jo++;
	    	else{
	    	   if(flag != 0){
	    		   sheet.mergeCells(2, flag, 2, flag+jo);
	    	   }
	    	   jo = 0;
	    	   flag = k;
	    	}
	    	
	    	dId = dismissal[l].getDepartment_ID();
			
			l++;
		}
		// last cell
	    sheet.mergeCells(2, flag, 2, flag+jo);
		
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();
			       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return Msg.translate(m_ctx, "Success");
	}
	
	private Dismissal[] getLoad(Timestamp startdate, Timestamp enddate, String subtype){
		
		Dismissal values = null;
		List<Dismissal> list = new ArrayList<Dismissal>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		List<Object> param = new ArrayList<Object>();
		if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Dismissal) && subtype != null){
			param.add(subtype); param.add(subtype);
		}
		param.add(startdate);
		param.add(enddate);
				
		try
		{
			pstmt = DB.prepareStatement(getSql(subtype, isManagement),get_TrxName());
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new Dismissal();
				values.setDepartment_ID(rs.getInt(1));
				values.setStartdate(rs.getTimestamp(2));
				values.setCntDismissal(rs.getInt(3));
				values.setFullDismissal(rs.getDouble(4));
				list.add(values);
				values=null;
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new Dismissal[list.size()]);
	}
	
	private String getSql(String subtype, String isManagement){
		
		StringBuffer sql = new StringBuffer("WITH t1 as( \n");
		sql.append("SELECT tt.hr_department_id, (to_char(t.startdate,'yyyy-mm')||'-01')::date as startdate, count(*)::numeric(2,0) as cntDismissal \n");
		sql.append("FROM hrm_order t \n");
		sql.append("JOIN hrm_employee r on r.hrm_order_id=t.hrm_order_id \n");
		sql.append("LEFT JOIN hr_employee tt on tt.c_bpartner_id=r.c_bpartner_id \n");
		sql.append("	AND tt.enddate = (SELECT max(h.enddate) FROM hr_employee h WHERE h.c_bpartner_id=tt.c_bpartner_id) \n");
		sql.append("LEFT JOIN HR_JOB ON HR_JOB.HR_JOB_ID=tt.HR_JOB_ID \n");
		sql.append("WHERE t.hrm_ordertype='").append(X_hrm_order.HRM_ORDERTYPE_Dismissal).append("'");
		//
		if(orderType.equals(X_hrm_order.HRM_ORDERTYPE_Dismissal) && subtype != null)
			sql.append(" AND t.hrm_contractterminationtype=? \n");
		
		if(isManagement.equals("Y"))
			sql.append(" AND HR_JOB.ISMANAGEMENT='Y' \n");
		else if(isManagement.equals("N"))
			sql.append(" AND HR_JOB.ISMANAGEMENT='N' \n");
		
		sql.append("GROUP BY (to_char(t.startdate,'yyyy-mm')||'-01')::date, tt.hr_department_id \n");
		sql.append("ORDER BY tt.hr_department_id, (to_char(t.startdate,'yyyy-mm')||'-01')::date \n");
		sql.append("), t2 as( \n");
		sql.append("SELECT t.*, ( \n");
		sql.append("select count(*)::numeric(3,0) from hr_employee t \n");
		sql.append("join hr_contract r on r.c_bpartner_id=t.c_bpartner_id and r.hrm_contracttype='1' \n");
		sql.append("where t.isactive='Y' and r.hrm_trialperiod_ID is null and r.isactive='Y' \n");
		sql.append("and t.startdate = (SELECT MAX(H.startdate) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=t.C_BPARTNER_ID and h.startdate <= "+DB.TO_DATE(endDate, true)+") \n");
		sql.append("and t.hr_department_id = t.hr_department_id and \n");
		sql.append("r.validfrom = (SELECT MAX(c.validFrom) FROM HR_CONTRACT C WHERE C.C_BPARTNER_ID=t.C_BPARTNER_ID and c.validFrom <= "+DB.TO_DATE(endDate, true)+") \n");
		sql.append(") as fullDismissal FROM t1 t \n");
		sql.append(") \n");
		sql.append("SELECT * FROM t2 WHERE startdate BETWEEN ? AND ? ");
		
		return 	sql.toString();
	}
	
	class Dismissal{
		
		private int Department_ID;
		private Timestamp startdate;
		private int cntDismissal;
		private double fullDismissal; 
		
		public int getDepartment_ID() {
			return Department_ID;
		}

		public void setDepartment_ID(int department_ID) {
			Department_ID = department_ID;
		}

		public Timestamp getStartdate() {
			return startdate;
		}

		public void setStartdate(Timestamp startdate) {
			this.startdate = startdate;
		}

		public int getCntDismissal() {
			return cntDismissal;
		}

		public void setCntDismissal(int cntDismissal) {
			this.cntDismissal = cntDismissal;
		}

		public double getFullDismissal() {
			return fullDismissal;
		}

		public void setFullDismissal(double fullDismissal) {
			this.fullDismissal = fullDismissal;
		}
		
	}

}
